package god.dream.notice.dao;

import god.dream.notice.model.Notice;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

public class SpringJdbcNoticeDao extends SimpleJdbcDaoSupport implements
		NoticeDAO {
	public int insert(Notice notice) throws DataAccessException {
		String query = "INSERT INTO "
				+ "NOTICE ( N_ID, N_TITLE, N_CONTENT, N_DATE ) "
				+ "VALUES ( ?, ?, ?, ? )";
		return getSimpleJdbcTemplate().update(query, notice.insert());
	}

	public int update(Notice notice) throws DataAccessException {
		String query = "UPDATE NOTICE "
				+ "SET N_TITLE=?, N_CONTENT=?, N_DATE=? " + "WHERE N_ID=?";
		return getSimpleJdbcTemplate().update(query, notice.update());
	}

	public int delete(int id) throws DataAccessException {
		String query = "DELETE FROM NOTICE WHERE N_ID=?";
		return getSimpleJdbcTemplate().update(query, new Object[] { id });
	}

	public Notice findNotice(int id) throws DataAccessException {
		String query = "SELECT * " + "FROM NOTICE WHERE N_ID=?";
		ParameterizedRowMapper<Notice> rowMapper = getRowMapper();

		try {
			return getSimpleJdbcTemplate().queryForObject(query, rowMapper,
					new Object[] { id });
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	public List<Notice> findNoticeList() throws DataAccessException {
		String sql = "SELECT * FROM NOTICE";
		ParameterizedRowMapper<Notice> rowMapper = getRowMapper();

		return getSimpleJdbcTemplate().query(sql, rowMapper);
	}

	private ParameterizedRowMapper<Notice> getRowMapper() {
		ParameterizedRowMapper<Notice> rowMapper = new ParameterizedRowMapper<Notice>() {
			public Notice mapRow(ResultSet rs, int rownum) throws SQLException {
				Notice notice = new Notice();
				notice.setId(rs.getInt("N_ID"));
				notice.setTitle(rs.getString("N_TITLE"));
				notice.setContent(rs.getString("N_CONTENT"));
				notice.setDate(rs.getDate("N_DATE").toString());
				return notice;
			}
		};
		return rowMapper;
	}

}
